﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DAL_Product
/// </summary>
public class DAL_Product
{
    DbProvider data = new DbProvider();
    
    public DataTable Product_GetByAll()
    {
        DataTable dt = new DataTable();
        string sql = "select * from Product where [Status]= '1' Order by [Order]";
        try
        {
            dt = data.ExecuteQuery(sql);

        }
        catch (Exception)
        {

            throw;
        }
        return dt;
    }
    public DataTable Product_GetByTop(int top, string where, string order)
    {
        DataTable dt = new DataTable();
        //string sql = string.Format("select top {0} * from Product where = '{1}' Order by [{2}] DESC,[Order]", top, where,order);
        string sql = "";
        if (top <= 0)
        {
            sql += "SELECT * FROM Product";
        }
        else
        {
            sql += "SELECT TOP "+top+" * FROM Product";
        }
        if (where != "")
        {
            sql += " WHERE " + where;
        }
        if (order != "")
        {
            sql += " Order by " + order;
        }
        try
        {
            dt = data.ExecuteQuery(sql);
           
        }
        catch (Exception)
        {
            
            throw;
        }
        return dt;
    }
    public DataTable Product_GetByID(string id)
    {
        DataTable dt = new DataTable();
        string sql = string.Format("select * from Product where Id = {0} and [Status]= '1'", id);
        try
        {
            dt = data.ExecuteQuery(sql);

        }
        catch (Exception)
        {

            throw;
        }
        return dt;
    }

    public DataTable Product_Insert(DTO_Product obj)
    {
        DataTable dt = new DataTable();
        string sql = string.Format("INSERT INTO [Product] VALUES ('{0}','{1}',N'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", obj.Manufacturer_Id, obj.GroupProduct_Id, obj.Name, obj.Detail, obj.Price, obj.Image, obj.View, obj.Buy, obj.Date, obj.Order, obj.Status);
        //string sql = string.Format("INSERT INTO [Product] VALUES ('{0}','{1}','{2}','{3}','{4}')", obj.Manufacturer_Id, obj.GroupProduct_Id, obj.Name, obj.Detail, obj.Price);
        try
        {
            data.ExecuteNonQuery(sql);
        }
        catch (Exception)
        {
            
            throw;
        }
        return dt;
    }
    public DataTable Product_UpdateByAll(string Id, DTO_Product obj)
    {
        DataTable dt = new DataTable();
        string sql = string.Format("UPDATE [Product] SET [Manufacturer_Id] = '{0}',GroupProduct_Id = '{1}',[Name] = '{2}',[Detail] = N'{3}',[Price] = '{4}',[Image] = '{5}',[View] = '{6}',[Buy] = '{7}',[Date] = '{8}',[Order] = '{9}',[Status] = '{10}' WHERE [Id] = '{11}'", obj.Manufacturer_Id, obj.GroupProduct_Id, obj.Name, obj.Detail, obj.Price, obj.Image, obj.View, obj.Buy, obj.Date, obj.Order, obj.Status,Id);

        try
        {
            data.ExecuteNonQuery(sql);
        }
        catch (Exception)
        {

            throw;
        }
        return dt;
    }
    public DataTable Product_Update(string Id, string Set)
    {
        DataTable dt = new DataTable();
        string sql = string.Format("UPDATE [Product] SET {0} WHERE [Id] = '{1}'",Set,Id);

        try
        {
            data.ExecuteNonQuery(sql);
        }
        catch (Exception)
        {

            throw;
        }
        return dt;
    }
    public DataTable Product_Delete(string Id)
    {
        DataTable dt = new DataTable();
        string sql = string.Format("DELETE FROM Product WHERE Id = '{0}'", Id);

        try
        {
            data.ExecuteNonQuery(sql);
        }
        catch (Exception)
        {

            throw;
        }
        return dt;
    }
    public DataTable Product_Detail(string id)
    {
        DataTable dt = new DataTable();
        string sql = string.Format("select Manufacturer.Name as NSX,Product.Id as ID,Product.Name as TenSP,Product.GroupProduct_Id as NhomSP,Product.Detail as Chitiet,Product.Price as Gia,Product.Image as Hinhanh,Product.[View] as Hienthi,Product.Buy as Mua,Product.Date as Ngay,Product.[Order] as Thutu,Product.Status as Trangthai from Product inner join Manufacturer on product.Manufacturer_Id=Manufacturer.id where product.Id='{0}' Order by Product.[Order]", id);
        try
        {
            dt = data.ExecuteQuery(sql);

        }
        catch (Exception)
        {

            throw;
        }
        return dt;
    }
}